﻿#Region "Namespaces"
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports Microsoft.Win32
Imports System.Xml
Imports System.IO
'In My Project, under references, click ADD button, and add COM reference Microsoft Excel 12.0 Object Library, then the following imports.
Imports Microsoft.Office.Core
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop
'Imports System.text is for copylistviewtoclipboard function
Imports System.Text
'Imports System.Diagnostics.Process is for starting external applications such as minitab
Imports System.Diagnostics.Process
'Imports Mtb
Imports System.Drawing
Imports System.Drawing.Printing
Imports System
Imports System.Collections
Imports System.Collections.Generic
Imports System.Windows.Forms

#End Region

Public Class Search_Results
    Public Shared Se_Re As Form
    Dim rowClicked As Integer

#Region "Print DataGridView"
    Private oStringFormat As StringFormat
    Private oStringFormatComboBox As StringFormat
    Private oButton As System.Windows.Forms.Button
    Private oCheckbox As System.Windows.Forms.CheckBox
    Private oComboBox As ComboBox
    Private nTotalWidth As Int16
    Private nRowPos As Int16
    Private NewPage As Boolean
    Private nPageNo As Int16
    Private Header As String = "Engineering Sample Build Database"
    Private sUserName As String = ""

    Private Sub PrintDocument1_BeginPrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs) Handles PrintDocument1.BeginPrint
        oStringFormat = New StringFormat
        oStringFormat.Alignment = StringAlignment.Near
        oStringFormat.LineAlignment = StringAlignment.Center
        oStringFormat.Trimming = StringTrimming.EllipsisCharacter
        oStringFormatComboBox = New StringFormat
        oStringFormatComboBox.LineAlignment = StringAlignment.Center
        oStringFormatComboBox.FormatFlags = StringFormatFlags.NoWrap
        oStringFormatComboBox.Trimming = StringTrimming.EllipsisCharacter
        oButton = New System.Windows.Forms.Button
        oCheckbox = New System.Windows.Forms.CheckBox
        oComboBox = New ComboBox
        nTotalWidth = 0
        For Each oColumn As DataGridViewColumn In SR.DGV_Search_Results.Columns
            nTotalWidth += oColumn.Width
        Next
        nPageNo = 1
        NewPage = True
        nRowPos = 0
    End Sub
    Private Sub PrintDocument1_PrintPage(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintPageEventArgs) Handles PrintDocument1.PrintPage
        Static oColumnLefts As New ArrayList
        Static oColumnWidths As New ArrayList
        Static oColumnTypes As New ArrayList
        Static nHeight As Int16
        Dim nWidth, i, nRowsPerPage As Int16
        Dim nTop As Int16 = e.MarginBounds.Top
        Dim nLeft As Int16 = e.MarginBounds.Left
        If nPageNo = 1 Then
            For Each oColumn As DataGridViewColumn In SR.DGV_Search_Results.Columns
                nWidth = CType(Math.Floor(oColumn.Width / nTotalWidth * nTotalWidth * (e.MarginBounds.Width / nTotalWidth)), Int16)
                nHeight = e.Graphics.MeasureString(oColumn.HeaderText, oColumn.InheritedStyle.Font, nWidth).Height + 11
                oColumnLefts.Add(nLeft)
                oColumnWidths.Add(nWidth)
                oColumnTypes.Add(oColumn.GetType)
                nLeft += nWidth
            Next
        End If
        Do While nRowPos < SR.DGV_Search_Results.Rows.Count - 1
            Dim oRow As DataGridViewRow = SR.DGV_Search_Results.Rows(nRowPos)
            If nTop + nHeight >= e.MarginBounds.Height + e.MarginBounds.Top Then
                DrawFooter(e, nRowsPerPage)
                NewPage = True
                nPageNo += 1
                e.HasMorePages = True
                Exit Sub
            Else
                If NewPage Then
                    ' Draw Header
                    e.Graphics.DrawString(Header, New System.Drawing.Font(SR.DGV_Search_Results.Font, FontStyle.Bold), Brushes.Black, e.MarginBounds.Left, e.MarginBounds.Top - e.Graphics.MeasureString(Header, New System.Drawing.Font(SR.DGV_Search_Results.Font, FontStyle.Bold), e.MarginBounds.Width).Height - 13)
                    ' Draw Columns
                    nTop = e.MarginBounds.Top
                    i = 0
                    For Each oColumn As DataGridViewColumn In SR.DGV_Search_Results.Columns
                        e.Graphics.FillRectangle(New SolidBrush(System.Drawing.Color.LightGray), New System.Drawing.Rectangle(oColumnLefts(i), nTop, oColumnWidths(i), nHeight))
                        e.Graphics.DrawRectangle(Pens.Black, New System.Drawing.Rectangle(oColumnLefts(i), nTop, oColumnWidths(i), nHeight))
                        e.Graphics.DrawString(oColumn.HeaderText, oColumn.InheritedStyle.Font, New SolidBrush(oColumn.InheritedStyle.ForeColor), New RectangleF(oColumnLefts(i), nTop, oColumnWidths(i), nHeight), oStringFormat)
                        i += 1
                    Next
                    NewPage = False
                End If
                nTop += nHeight
                i = 0
                For Each oCell As DataGridViewCell In oRow.Cells
                    If oColumnTypes(i) Is GetType(DataGridViewTextBoxColumn) OrElse oColumnTypes(i) Is GetType(DataGridViewLinkColumn) Then
                        e.Graphics.DrawString(oCell.Value.ToString, oCell.InheritedStyle.Font, New SolidBrush(oCell.InheritedStyle.ForeColor), New RectangleF(oColumnLefts(i), nTop, oColumnWidths(i), nHeight), oStringFormat)
                    ElseIf oColumnTypes(i) Is GetType(DataGridViewButtonColumn) Then
                        oButton.Text = oCell.Value.ToString
                        oButton.Size = New Size(oColumnWidths(i), nHeight)
                        Dim oBitmap As New Bitmap(oButton.Width, oButton.Height)
                        oButton.DrawToBitmap(oBitmap, New System.Drawing.Rectangle(0, 0, oBitmap.Width, oBitmap.Height))
                        e.Graphics.DrawImage(oBitmap, New System.Drawing.Point(oColumnLefts(i), nTop))
                    ElseIf oColumnTypes(i) Is GetType(DataGridViewCheckBoxColumn) Then
                        oCheckbox.Size = New Size(14, 14)
                        oCheckbox.Checked = CType(oCell.Value, Boolean)
                        Dim oBitmap As New Bitmap(oColumnWidths(i), nHeight)
                        Dim oTempGraphics As Graphics = Graphics.FromImage(oBitmap)
                        oTempGraphics.FillRectangle(Brushes.White, New System.Drawing.Rectangle(0, 0, oBitmap.Width, oBitmap.Height))
                        oCheckbox.DrawToBitmap(oBitmap, New System.Drawing.Rectangle(CType((oBitmap.Width - oCheckbox.Width) / 2, Int32), CType((oBitmap.Height - oCheckbox.Height) / 2, Int32), oCheckbox.Width, oCheckbox.Height))
                        e.Graphics.DrawImage(oBitmap, New System.Drawing.Point(oColumnLefts(i), nTop))
                    ElseIf oColumnTypes(i) Is GetType(DataGridViewComboBoxColumn) Then
                        oComboBox.Size = New Size(oColumnWidths(i), nHeight)
                        Dim oBitmap As New Bitmap(oComboBox.Width, oComboBox.Height)
                        oComboBox.DrawToBitmap(oBitmap, New System.Drawing.Rectangle(0, 0, oBitmap.Width, oBitmap.Height))
                        e.Graphics.DrawImage(oBitmap, New System.Drawing.Point(oColumnLefts(i), nTop))
                        e.Graphics.DrawString(oCell.Value.ToString, oCell.InheritedStyle.Font, New SolidBrush(oCell.InheritedStyle.ForeColor), New RectangleF(oColumnLefts(i) + 1, nTop, oColumnWidths(i) - 16, nHeight), oStringFormatComboBox)
                    ElseIf oColumnTypes(i) Is GetType(DataGridViewImageColumn) Then
                        Dim oCellSize As System.Drawing.Rectangle = New System.Drawing.Rectangle(oColumnLefts(i), nTop, oColumnWidths(i), nHeight)
                        Dim oImageSize As Size = CType(oCell.Value, Image).Size
                        e.Graphics.DrawImage(oCell.Value, New System.Drawing.Rectangle(oColumnLefts(i) + CType(((oCellSize.Width - oImageSize.Width) / 2), Int32), nTop + CType(((oCellSize.Height - oImageSize.Height) / 2), Int32), CType(oCell.Value, Image).Width, CType(oCell.Value, Image).Height))
                    End If
                    e.Graphics.DrawRectangle(Pens.Black, New System.Drawing.Rectangle(oColumnLefts(i), nTop, oColumnWidths(i), nHeight))
                    i += 1
                Next
            End If
            nRowPos += 1
            nRowsPerPage += 1
        Loop
        DrawFooter(e, nRowsPerPage)
        e.HasMorePages = False
    End Sub
    Private Sub DrawFooter(ByVal e As System.Drawing.Printing.PrintPageEventArgs, ByVal RowsPerPage As Int32)
        Dim sPageNo As String = nPageNo.ToString + " of " + Math.Ceiling(SR.DGV_Search_Results.Rows.Count / RowsPerPage).ToString
        ' Right Align - User Name
        e.Graphics.DrawString(sUserName, SR.DGV_Search_Results.Font, Brushes.Black, e.MarginBounds.Left + (e.MarginBounds.Width - e.Graphics.MeasureString(sPageNo, SR.DGV_Search_Results.Font, e.MarginBounds.Width).Width), e.MarginBounds.Top + e.MarginBounds.Height + 7)
        ' Left Align - Date/Time
        e.Graphics.DrawString(Now.ToLongDateString + " " + Now.ToShortTimeString, SR.DGV_Search_Results.Font, Brushes.Black, e.MarginBounds.Left, e.MarginBounds.Top + e.MarginBounds.Height + 7)
        ' Center  - Page No. Info
        e.Graphics.DrawString(sPageNo, SR.DGV_Search_Results.Font, Brushes.Black, e.MarginBounds.Left + (e.MarginBounds.Width - e.Graphics.MeasureString(sPageNo, SR.DGV_Search_Results.Font, e.MarginBounds.Width).Width) / 2, e.MarginBounds.Top + e.MarginBounds.Height + 31)
    End Sub

#End Region

#Region "Menu Items"

    Private Sub MI_Export_Excel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MI_Export_Excel.Click
        Me.Cursor = Cursors.WaitCursor
Export:
        '----------------------------------------------------------------------
        'Use CopyAllRowsToClipboard function to copy data from the datagridview and paste it into Excel
        DGV_Search_Results.Visible = False
        SR.DGV_Search_Results.RowHeadersVisible = False
        CopyAllRowsToClipboard()
        DGV_Search_Results.Visible = True


        '==============================Open Excel Application=========================================
        Dim ExcelReport As Excel.Application
        ExcelReport = New Excel.Application

        '-- Setting this to false and then resetting it to true at the end will stop 
        '-- a user clicking around in excel and causing a possible error 
        ExcelReport.Visible = False

        'Open Personal_xlsb file from excel to load macros
        'Dim Personal_XLSB_Path As String = ExcelReport.StartupPath.ToString + "\Personal.xlsb"
        'Dim Personal_XLS_Path As String = ExcelReport.StartupPath.ToString + "\Personal.xls"

        'If System.IO.File.Exists(Personal_XLSB_Path) Then
        '    ExcelReport.Workbooks.Open(Personal_XLSB_Path)
        '    'Hide workbook
        'Else
        '    If System.IO.File.Exists(Personal_XLS_Path) Then
        '        ExcelReport.Workbooks.Open(Personal_XLS_Path)
        '    End If
        'End If


        'Open new blank workbook and paste data into it
        ExcelReport.Workbooks.Add()

        ExcelReport.Worksheets("Sheet1").select()

        'Paste the data.
        ExcelReport.Worksheets(1).Range("A1").Select()
        ExcelReport.Worksheets(1).Paste()

        ExcelReport.Range("a1").Select() 'clears highlight cells in excel
        ExcelReport.Worksheets(1).Columns().ColumnWidth = 20 'Sets all columns in excel to width of 11

        'Fill color of first three rows in Excel
        ExcelReport.Worksheets(1).rows(1).interior.colorindex = 43 'Light Green for Column Headers
        'ExcelReport.Worksheets(1).rows(2).interior.colorindex = 36 'Light Yellow for Limits Min
        'ExcelReport.Worksheets(1).rows(3).interior.colorindex = 36 'Light Yellow for Limits Mas

        'Add Border around Column names row
        ExcelReport.Worksheets(1).rows(1).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic)

        'Change font of column header names to bold
        ExcelReport.Worksheets(1).rows(1).font.bold = True

        'Freeze First 3 Rows
        'ExcelReport.Worksheets(1).rows(4).select()
        'ExcelReport.Worksheets(1).Application.ActiveWindow.FreezePanes = True
        'ExcelReport.Range("a1").Select()

        'Save Excel worksheet
        'ExcelReport.Workbooks.Add.SaveAs(Excel_FilenamePath) 'Testing line

        'Show Excel
        ExcelReport.Visible = True

        '========================================================================================
        'Get PID of excel window, when it closes, release excel objects
        'Currently Excel object will release when this application is closed.
        'If excel workbook is still open when this application is closed, the excel object will still be alive, but will
        'be released when the workbook is closed by the user.
        'However, if the excel workbook is closed and this application is still open, the excel object will not be released.
        'If this application is then closed after the workbook has been closed, the excel object will be released.
        '=========================================================================================

Finish:
        Me.Cursor = Cursors.Default
    End Sub
    Private Sub MI_Copy_To_Clipboard_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MI_Copy_To_Clipboard.Click
        CopyAllRowsToClipboard()
    End Sub
    Private Sub MI_Copy_Selected_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MI_Copy_Selected.Click
        DGV_Search_Results.Visible = False

        '==================================================================================================
        '==================================================================================================
        'Remove un-selected columns
        'Loop Through each cell of each row of each column. If a cell is selected, go to next column.
        'If cell is not selected, go to next row's cell until end of column
        Dim DGV_columncount As Integer
        DGV_columncount = DGV_Search_Results.Columns.GetColumnCount(DataGridViewElementStates.None)

        For i = 0 To DGV_columncount - 1
            'Find out if a column has a selected cell in it. If so, go to next column
            Dim dgv_rowcount As Integer
            dgv_rowcount = DGV_Search_Results.Rows.GetRowCount(DataGridViewElementStates.None)
            For j = 0 To dgv_rowcount - 1
                If DGV_Search_Results.Rows(j).Cells(i).Selected = True Then
                    GoTo End_Column_Loop
                Else
                    GoTo End_Row_Loop
                End If
End_Row_Loop:
            Next j
            'Hide column since we now know it is empty
            DGV_Search_Results.Columns(i).Visible = False
End_Column_Loop:
        Next
        '==================================================================================================
        '==================================================================================================
        Dim strClipboard As String = String.Empty


CopyWithColumnHeaders:
        'Set ClipBoardCopyMode for the datagridview to not include header text
        Me.DGV_Search_Results.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
        If Me.DGV_Search_Results.GetCellCount(DataGridViewElementStates.Selected) > 0 Then
            Try
                ' Add the selection to the clipboard.
                Clipboard.SetDataObject(Me.DGV_Search_Results.GetClipboardContent())
                strClipboard = Clipboard.GetText()
                Clipboard.SetText(strClipboard)
            Catch ex As System.Runtime.InteropServices.ExternalException
                MsgBox(ex.message)
            End Try
        End If


        'Show all rows again as data has been copied
        For i = 0 To DGV_columncount - 1
            If DGV_Search_Results.Columns(i).Visible = False Then
                DGV_Search_Results.Columns(i).Visible = True
            End If
        Next
        DGV_Search_Results.Visible = True
    End Sub
    Private Sub MI_Exit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MI_Exit.Click
        Me.Close()
    End Sub
    Private Sub MI_Print_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MI_Print.Click
        Dim dlg As New PrintPreviewDialog()
        dlg.Document = PrintDocument1
        dlg.ShowDialog()
    End Sub

#End Region

#Region "Context Menu Items"

    Private Sub CopyToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CopyToolStripMenuItem.Click
        Dim strClipboard As String = String.Empty

CopyWithColumnHeaders:
        'Set ClipBoardCopyMode for the datagridview to not include header text
        Me.DGV_Search_Results.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
        If Me.DGV_Search_Results.GetCellCount(DataGridViewElementStates.Selected) > 0 Then
            Try
                ' Add the selection to the clipboard.
                Clipboard.SetDataObject(Me.DGV_Search_Results.GetClipboardContent())
                strClipboard = Clipboard.GetText()
                Clipboard.SetText(strClipboard)
            Catch ex As System.Runtime.InteropServices.ExternalException
                MsgBox(ex.message)
            End Try
        End If
        GoTo Finish

Finish:
    End Sub
    Private Sub CopyToExcelToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CopyToExcelToolStripMenuItem.Click
        'Hide Form
        DGV_Search_Results.Visible = False
        Me.Cursor = Cursors.WaitCursor
        'Remove Row Headers so extra column is not copied to clipboard
        DGV_Search_Results.RowHeadersVisible = False

        '==================================================================================================
        '==================================================================================================
        'Remove un-selected columns
        'Loop Through each cell of each row of each column. If a cell is not selected, go to next
        'hide the cell
        Dim DGV_columncount As Integer
        DGV_columncount = DGV_Search_Results.Columns.GetColumnCount(DataGridViewElementStates.None)

        For i = 0 To DGV_columncount - 1
            'Find out if a column has a selected cell in it. If so, go to next column
            Dim dgv_rowcount As Integer
            dgv_rowcount = DGV_Search_Results.Rows.GetRowCount(DataGridViewElementStates.None)
            For j = 0 To dgv_rowcount - 1
                If DGV_Search_Results.Rows(j).Cells(i).Selected = True Then
                    GoTo End_Column_Loop
                Else
                    GoTo End_Row_Loop
                End If
End_Row_Loop:
            Next j
            'Hide column since we now know it is empty
            DGV_Search_Results.Columns(i).Visible = False
End_Column_Loop:
        Next
        '==================================================================================================
        '==================================================================================================

        'Copy Selected cells/columns
        Dim strClipboard As String = String.Empty
        Me.DGV_Search_Results.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
        If Me.DGV_Search_Results.GetCellCount(DataGridViewElementStates.Selected) > 0 Then
            Try
                ' Add the selection to the clipboard.
                Clipboard.SetDataObject(Me.DGV_Search_Results.GetClipboardContent())
                strClipboard = Clipboard.GetText()
                Clipboard.SetText(strClipboard)
            Catch ex As System.Runtime.InteropServices.ExternalException
                MsgBox(ex.message)
            End Try
        End If

        'Show all rows again as data has been copied
        For i = 0 To DGV_columncount - 1
            If DGV_Search_Results.Columns(i).Visible = False Then
                DGV_Search_Results.Columns(i).Visible = True
            End If
        Next
        DGV_Search_Results.Visible = True


        'Open Excel Application
        Dim ExcelReport As Excel.Application
        ExcelReport = New Excel.Application

        '-- Setting this to false and then resetting it to true at the end will stop 
        '-- a user clicking around in excel and causing a possible error 
        ExcelReport.Visible = False

        'Open Personal_xlsb file from excel to load macros
        'Dim Personal_XLSB_Path As String = ExcelReport.StartupPath.ToString + "\Personal.xlsb"
        'Dim Personal_XLS_Path As String = ExcelReport.StartupPath.ToString + "\Personal.xls"

        'If System.IO.File.Exists(Personal_XLSB_Path) Then
        '    ExcelReport.Workbooks.Open(Personal_XLSB_Path)
        '    'Hide workbook
        'Else
        '    If System.IO.File.Exists(Personal_XLS_Path) Then
        '        ExcelReport.Workbooks.Open(Personal_XLS_Path)
        '    End If
        'End If

        'Open a blank excel workbook and paste the data into it.
        ExcelReport.Workbooks.Add()
        ExcelReport.Worksheets("Sheet1").select()

        'Paste the data.
        ExcelReport.Worksheets(1).Range("A1").Select()
        ExcelReport.Worksheets(1).Paste()
        ExcelReport.Range("a1").Select() 'clears highlight cells in excel
        ExcelReport.Worksheets(1).Columns().ColumnWidth = 20 'Sets all columns in excel to width of 11

        'Fill color of first row in Excel
        ExcelReport.Worksheets(1).rows(1).interior.colorindex = 43 'Light Green for Column Headers

        'Add Border around Column names row
        ExcelReport.Worksheets(1).rows(1).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic)

        'Change font of column header names to bold
        ExcelReport.Worksheets(1).rows(1).font.bold = True

        'Freeze First 3 Rows
        'ExcelReport.Worksheets(1).rows(2).select()
        'ExcelReport.Worksheets(1).Application.ActiveWindow.FreezePanes = True
        'ExcelReport.Range("a1").Select()

        'Show Excel
        Me.Cursor = Cursors.Default
        ExcelReport.Visible = True

    End Sub
    Private Sub ModifyRecordToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ModifyRecordToolStripMenuItem.Click
        'Extract row data and insert into Modify_Record fields
        Extract_RowData()

        'Open Modify_Record Form and enter row data
        Modify_Record.ShowDialog()
    End Sub
    Private Sub DeleteRecordToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteRecordToolStripMenuItem.Click
        Dim DGV_columncount As Integer
        DGV_columncount = DGV_Search_Results.Columns.GetColumnCount(DataGridViewElementStates.None)

        For i = 0 To DGV_columncount - 1
            'Find out if a column has a selected cell in it. If so, go to next column
            Dim dgv_rowcount As Integer
            dgv_rowcount = DGV_Search_Results.Rows.GetRowCount(DataGridViewElementStates.None)
            For j = 0 To dgv_rowcount - 1

                If j = rowClicked Then
                    Dim colName As String = DGV_Search_Results.Columns(i).Name.ToString
                    Dim cellValue As String = DGV_Search_Results.Rows(j).Cells(i).Value.ToString

                    If colName = "Job_ID" Then
                        Delete_Record.TB_JobID.Text = cellValue
                    End If

                    If colName = "Project_Name" Then
                        Delete_Record.TB_ProjName.Text = cellValue
                    End If

                    If colName = "Requested_Date" Then
                        Delete_Record.TB_ReqDate.Text = cellValue
                    End If

                    If colName = "Completion_Date" Then
                        Delete_Record.TB_CompDate.Text = cellValue
                    End If

                    If colName = "Ship_Date" Then
                        Delete_Record.TB_ShipDate.Text = cellValue
                    End If

                    If colName = "Build_Quantity" Then
                        Delete_Record.TB_BuildQty.Text = cellValue
                    End If

                    If colName = "Build_Type" Then
                        Delete_Record.TB_BuildType.Text = cellValue
                    End If

                    If colName = "BOM_Rev" Then
                        Delete_Record.TB_BOMRev.Text = cellValue
                    End If

                    If colName = "PCB_Rev" Then
                        Delete_Record.TB_PCBRev.Text = cellValue
                    End If

                    If colName = "PCB_ZNN" Then
                        Delete_Record.TB_PCBZNN.Text = cellValue
                    End If

                    If colName = "PCB_Manufacturer" Then
                        Delete_Record.TB_PCBManu.Text = cellValue
                    End If

                    If colName = "PCB_Assembler" Then
                        Delete_Record.TB_PCBAssembler.Text = cellValue
                    End If

                    If colName = "LED_Specification" Then
                        Delete_Record.TB_LEDSpec.Text = cellValue
                    End If

                    If colName = "Special_Build_Instruction" Then
                        Delete_Record.TB_BuildIns.Text = cellValue
                    End If

                    If colName = "Customer" Then
                        Delete_Record.TB_Customer.Text = cellValue
                    End If

                    If colName = "Shipping_Record" Then
                        Delete_Record.TB_AttachFile.Text = cellValue
                    End If

                    If colName = "Status" Then
                        Delete_Record.TB_Status.Text = cellValue
                    End If

                    If colName = "Comments" Then
                        Delete_Record.TB_Comments.Text = cellValue
                    End If

                    GoTo End_Column_Loop
                End If
            Next j
End_Column_Loop:
        Next

        'Open Delete Record Form
        Delete_Record.ShowDialog()
    End Sub

#End Region

#Region "Functions"
    Private Sub SR_DGV_SearchResults_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DGV_Search_Results.CellContentClick
        If e.ColumnIndex = 15 Then
            'Retrieve file from SQL database
            'Delete Temp Path if it exists
            Try
                Dim Delete_FilenamePath As String = "C:\ESBD_Temp\"
                If (System.IO.Directory.Exists(Delete_FilenamePath)) Then
                    System.IO.Directory.Delete(Delete_FilenamePath, True)
                End If
            Catch ex As Exception
                Console.WriteLine("Files are still open")
            End Try


            'MessageBox.Show(DGV_Search_Results.Item(e.ColumnIndex, e.RowIndex).Value.ToString)
            Dim FileName As String = DGV_Search_Results.Item(e.ColumnIndex, e.RowIndex).Value.ToString
            Dim extension As String = ""
            Dim b As Byte()

            Try
                ESBD_Form.OpenSQLConnection()
                Dim connection As New SqlConnection(csb.ToString())
                cmd.Connection = connection
                connection.Open()

                cmd.CommandText = "Select Extension, Content from Attachments_TB where Filename = '" + FileName + "'"

                dr = cmd.ExecuteReader()
                If dr.Read() Then
                    extension = dr("Extension").ToString()
                    b = DirectCast(dr("Content"), [Byte]())
                    Dim writeStream As FileStream

                    Try
                        'Create Folder to save attached file if it does not exist
                        Dim Temp_FilenamePath As String = "C:\ESBD_Temp\"
                        If (Not System.IO.Directory.Exists(Temp_FilenamePath)) Then
                            System.IO.Directory.CreateDirectory(Temp_FilenamePath)
                        End If

                        'Create temp file and save into above location
                        writeStream = New FileStream(Temp_FilenamePath + FileName, FileMode.Create)
                        Dim writeBinay As New BinaryWriter(writeStream)
                        writeBinay.Write(b)
                        writeBinay.Close()

                        'Open temp file
                        Dim file As String = Temp_FilenamePath + FileName
                        If System.IO.File.Exists(file) Then
                            System.Diagnostics.Process.Start(file)
                        End If

                    Catch ex As Exception
                        MsgBox(ex.message)
                    End Try
                End If
                dr.Close()
                connection.Close()
                cmd.Parameters.Clear()
                connection.Close()

            Catch ex As Exception
                Dim connection As New SqlConnection(csb.ToString())
                connection.Close()
                MsgBox(ex.message)
            End Try

        Else

        End If
    End Sub
    Private Sub CopyAllRowsToClipboard()

        Try
            My.Computer.Clipboard.Clear()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

        'Remove Row Headers in datagridview
        DGV_Search_Results.RowHeadersVisible = False

        Dim strClipboard As String = String.Empty

        DGV_Search_Results.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
        DGV_Search_Results.SelectAll() 'select all cells in the dgv

        Clipboard.SetDataObject(Me.DGV_Search_Results.GetClipboardContent())
        strClipboard = Clipboard.GetText()
        Clipboard.SetText(strClipboard)
        DGV_Search_Results.ClearSelection()

        'Set ClipboardCopyMode back to not include header text
        DGV_Search_Results.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableWithoutHeaderText


    End Sub

    Private Sub Extract_RowData()
        Dim DGV_columncount As Integer
        DGV_columncount = DGV_Search_Results.Columns.GetColumnCount(DataGridViewElementStates.None)

        For i = 0 To DGV_columncount - 1
            'Find out if a column has a selected cell in it. If so, go to next column
            Dim dgv_rowcount As Integer
            dgv_rowcount = DGV_Search_Results.Rows.GetRowCount(DataGridViewElementStates.None)
            For j = 0 To dgv_rowcount - 1

                If j = rowClicked Then
                    Dim colName As String = DGV_Search_Results.Columns(i).Name.ToString
                    Dim cellValue As String = DGV_Search_Results.Rows(j).Cells(i).Value.ToString

                    If colName = "Job_ID" Then
                        Modify_Record.TB_JobID.Text = cellValue
                    End If

                    If colName = "Project_Name" Then
                        Modify_Record.cmb_ProjName.Text = cellValue
                    End If

                    If colName = "Requested_Date" Then
                        Modify_Record.TB_ReqDate.Text = cellValue
                    End If

                    If colName = "Completion_Date" Then
                        Modify_Record.TB_CompDate.Text = cellValue
                    End If

                    If colName = "Ship_Date" Then
                        Modify_Record.TB_ShipDate.Text = cellValue
                    End If

                    If colName = "Build_Quantity" Then
                        Modify_Record.TB_BuildQty.Text = cellValue
                    End If

                    If colName = "Build_Type" Then
                        Modify_Record.TB_BuildType.Text = cellValue
                    End If

                    If colName = "BOM_Rev" Then
                        Modify_Record.TB_BOMRev.Text = cellValue
                    End If

                    If colName = "PCB_Rev" Then
                        Modify_Record.TB_PCBRev.Text = cellValue
                    End If

                    If colName = "PCB_ZNN" Then
                        Modify_Record.TB_PCBZNN.Text = cellValue
                    End If

                    If colName = "PCB_Manufacturer" Then
                        Modify_Record.cmb_PCBManu.Text = cellValue
                    End If

                    If colName = "PCB_Assembler" Then
                        Modify_Record.cmb_PCBAssm.Text = cellValue
                    End If

                    If colName = "LED_Specification" Then
                        Modify_Record.TB_LEDSpec.Text = cellValue
                    End If

                    If colName = "Special_Build_Instruction" Then
                        Modify_Record.TB_BuildIns.Text = cellValue
                    End If

                    If colName = "Customer" Then
                        If cellValue <> "" Then
                            Dim cv_5 As String = cellValue
                            Dim ary_cv5 As String()
                            ary_cv5 = cv_5.Split(vbNewLine)
                            cv_5 = ary_cv5(0).ToString
                            Modify_Record.cmb_Customer.Text = cv_5
                        Else
                            Modify_Record.cmb_Customer.Text = ""
                        End If
                    End If

                    If colName = "Shipping_Record" Then
                        Modify_Record.TB_AttachFile.Text = cellValue
                    End If

                    If colName = "Status" Then
                        Modify_Record.cmb_Status.Text = cellValue
                    End If

                    If colName = "Comments" Then
                        Modify_Record.TB_Comments.Text = cellValue
                    End If

                    GoTo End_Column_Loop
                End If
            Next j
End_Column_Loop:
        Next
    End Sub

    Private Sub DGV_Search_Results_MouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles DGV_Search_Results.MouseDown
        'Find row mouse is on when right clicked and set row number to rowClicked
        Try
            If e.Button = System.Windows.Forms.MouseButtons.Right Then
                rowClicked = DGV_Search_Results.HitTest(e.Location.X, e.Location.Y).RowIndex
                DGV_Search_Results.Rows(rowClicked).Selected = True
            End If
        Catch ex As Exception
            'MsgBox(ex.message)
        End Try
    End Sub
#End Region

#Region "Form Load/Close"
    Sub Search_Results_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Se_Re = Me
        Menu = MainMenu1

        'Loop through columns in datagridview and make all columns not sortable
        For i = 0 To Number_of_Columns - 1
            SR.DGV_Search_Results.Columns(i).SortMode = DataGridViewColumnSortMode.Programmatic
            If i = 14 Then
                SR.DGV_Search_Results.Columns(i).DefaultCellStyle.WrapMode = DataGridViewTriState.True
            End If
        Next

        'Enter Customer Details in Customer Column
        Try
            Dim k As Integer 'k is the row number
            While k <= SR.DGV_Search_Results.Rows.Count - 1
                Dim z As Integer = 0 'z is the cell number in the row
                While z <= SR.DGV_Search_Results.Rows(k).Cells.Count - 1
                    If z = 14 Then
                        If Not SR.DGV_Search_Results.Rows(k).Cells(z).Value Is DBNull.Value And SR.DGV_Search_Results.Rows(k).Cells(z).Value <> "" Then
                            'Get Customer Details
                            Dim curName As String = SR.DGV_Search_Results.Rows(k).Cells(z).Value
                            Dim cust As String = ""
                            Dim SQLResult As Object
                            Dim name1 As String = ""
                            Dim add1 As String = ""
                            Dim add2 As String = ""
                            Dim city As String = ""
                            Dim state As String = ""
                            Dim country As String = ""
                            Dim zip As String = ""
                            Dim notes As String = ""

                            ESBD_Form.OpenSQLConnection()
                            Dim conn As New SqlConnection(csb.ToString())
                            cmd.Connection = conn
                            conn.Open()

                            'Find name1
                            cmd.CommandText = "Select Name from CUST_TB where ""Name"" = '" + curName + "'"
                            SQLResult = cmd.ExecuteScalar
                            If Not SQLResult Is DBNull.Value Then
                                name1 = SQLResult
                                cust = name1
                            End If

                            'Find address1
                            cmd.CommandText = "Select Street1 from CUST_TB where ""Name"" = '" + curName + "'"
                            SQLResult = cmd.ExecuteScalar
                            If Not SQLResult Is DBNull.Value And SQLResult <> "" Then
                                add1 = SQLResult
                                cust = cust + vbCrLf + add1
                            End If

                            'Find address2
                            cmd.CommandText = "Select Street2 from CUST_TB where ""Name"" = '" + curName + "'"
                            SQLResult = cmd.ExecuteScalar
                            If Not SQLResult Is DBNull.Value And SQLResult <> "" Then
                                add2 = SQLResult
                                cust = cust + vbCrLf + add2
                            End If

                            'Find city
                            cmd.CommandText = "Select City from CUST_TB where ""Name"" = '" + curName + "'"
                            SQLResult = cmd.ExecuteScalar
                            If Not SQLResult Is DBNull.Value And SQLResult <> "" Then
                                city = SQLResult
                                cust = cust + vbCrLf + city
                            End If

                            'Find state
                            cmd.CommandText = "Select State from CUST_TB where ""Name"" = '" + curName + "'"
                            SQLResult = cmd.ExecuteScalar
                            If Not SQLResult Is DBNull.Value And SQLResult <> "" Then
                                state = SQLResult
                                cust = cust + vbCrLf + state
                            End If

                            'Find country
                            cmd.CommandText = "Select Country from CUST_TB where ""Name"" = '" + curName + "'"
                            SQLResult = cmd.ExecuteScalar
                            If Not SQLResult Is DBNull.Value And SQLResult <> "" Then
                                country = SQLResult
                                cust = cust + vbCrLf + country
                            End If

                            'Find zip
                            cmd.CommandText = "Select ZipCode from CUST_TB where ""Name"" = '" + curName + "'"
                            SQLResult = cmd.ExecuteScalar
                            If Not SQLResult Is DBNull.Value And SQLResult <> "" Then
                                zip = SQLResult
                                cust = cust + vbCrLf + zip
                            End If

                            'Find notes
                            cmd.CommandText = "Select Notes from CUST_TB where ""Name"" = '" + curName + "'"
                            SQLResult = cmd.ExecuteScalar
                            If Not SQLResult Is DBNull.Value And SQLResult <> "" Then
                                notes = SQLResult
                                cust = cust + vbCrLf + notes
                            End If

                            SR.DGV_Search_Results.Rows(k).Cells(z).Value = cust
                            conn.Close()
                        End If
                    End If
                    z = z + 1
                End While
                k = k + 1
            End While
        Catch ex As Exception
            Dim conn As New SqlConnection(csb.ToString())
            conn.Close()
            MsgBox(ex.message)
        End Try

        'Convert all cells in column Shipping_Record to link cells
        Dim j As Integer 'j is the row number
        While j <= SR.DGV_Search_Results.Rows.Count - 1
            Dim y As Integer = 0 'y is the cell number in the row
            While y <= SR.DGV_Search_Results.Rows(j).Cells.Count - 1
                If y = 15 Then
                    If Not SR.DGV_Search_Results.Rows(j).Cells(y).Value Is DBNull.Value And SR.DGV_Search_Results.Rows(j).Cells(y).Value <> "" Then
                        'Change cell type to link
                        Dim linkCell As New DataGridViewLinkCell()
                        SR.DGV_Search_Results.Rows(j).Cells(y) = linkCell
                        linkCell.VisitedLinkColor = Color.Blue
                        linkCell.Style.SelectionBackColor = Color.Transparent
                    End If
                End If
                y = y + 1
            End While
            j = j + 1
        End While
        SR.DGV_Search_Results.SelectionMode = DataGridViewSelectionMode.ColumnHeaderSelect

        'Hide RowHeaders
        SR.DGV_Search_Results.RowHeadersVisible = False
        SR.DGV_Search_Results.AutoResizeRows()
        SR.DGV_Search_Results.AutoResizeColumns()

    End Sub
#End Region

End Class